Chasing unicorns!

0 dataloss and 99.999% availability

PGConf US - New York

Who am I

• Lætitia Avrot
• mydbanotebook.org
• PostgreSQL recognized Contributer
• founder of #PostgresWomen
• EDB Field CTO
• @l_avrot

Chasing unicorns

• About dataloss and downtime
• CAP and PACELC theorems
• Some typical architectures

0 dataloss

• Does not exist
• Sorry :-)
• Different meanings

Allowed failure time

Anythink can break

Anythink can break

Anythink can break

CAP theorem

When designing distributed web services, there are three properties that are commonly desired: consisteny, availability, and partition tolerance. It is impossible to achieve all three.

CAP theorem

Consistency

Each node will see the last (committed) version of the data.

Availability

The system will always answer something different than an error message.

Network partitioning

The network will be allowed to lose arbitrarily many messages sent from one node to another without altering the system answers.

CAP theorem

• impossibility to have all 3
• you can secure 2
• then stretch to the third

RDBMS are CA

RDBMS in single node configuration are CA

RDBMS are CA

RDBMS with asynchronous streaming replication are AP

RDBMS are CA

RDBMS with synchronous streaming replication are neither CP, neither AP

PACELC theorem

In case of network partitioning (P) in a distributed computer system, one has to choose between availability (A) and consistency (C),

but else (E), even when the system is running normally in the absence of partitions, one has to choose between latency (L) and consistency (C).

Latency

Latency is the amount of time the system needs to answer when it's smaller than a request timeout.

Blueprints

• PostgreSQL
• These are only examples
• Check the weakest link

Just some vocabulary

• Queen: database server who can read and write
• Princess: database server who's waiting for the Queen to fall to take its place
• Worker: database server who's only reading

Dataloss

High availability

Haute disponibilité

A standard architecture

Why standard??

• Fits in a lot of budgets
• One or two datacenter•s
• Most of the time, ~30 minutes RTO
• Most of the time, ~200ms RPO

The database servers

The database servers

• Streaming asynchronous replication
• One or two datacenter•s
• Blue/green denomination
• Connection pooler

Other tools...

Other tools...

• Monitoring
• Backup
• Backup
• Configuration file maintenance

Favoring fast reads

Some context

• Medical grade data
• High read availability
• Lower down dataloss risk

Basic architecture

Basic architecture

• 1 Queen
• 1 Princess
• Several workers

The backups

The backups

• Backup of configuration files on git
• Physical pbackups + WAL archiving
• Daily test on "cold" standby

Let's double the datacenters

Let's double the datacenters

• Synnchronous princesses with a quorum of 1
• Backup redondancy

...And all the rest!

...And all the rest!

• Adding a monitoring server
• Management of oldest data
• Online archiving
• Offline archiving

An acceptable trade-off

Favour availability

Favour availability

• Bank
• A lot of writes

BDR

BDR

• Theoritically, 4 writing nodes
• Only 2 writing nodes at a time
• A witness node to ensure majority

Logical replication

Logical replication

• Adding a logical replication
• Logical princess nodes
• Less network bandwidth than 2 new Queens

High availability

High availability

• PgBouncer as a connection pooler
• Ha Proxy to redirect to the good Lead Queen

With backups

With backups

• Physical backups of the Lead queens
• Backups need to be tested

• The database is not the only element that can fail
• You are not Google
• Figure out what your RPO is
• Figure out what your RTO is

Unicorns are more real than 0-dataloss and 99.999% availability.